import pandas as pd
import numpy as np

def preprocess_data(input_file, output_file):
    # 读取原始数据
    df = pd.read_csv(input_file,encoding='gbk')
    
    # 转换日期格式
    df['date'] = pd.to_datetime(df['date'], format='%Y%m%d')
    
    # 创建城市污染物列
    pollutants = ['AQI', 'PM2.5', 'PM10', 'SO2', 'NO2', 'O3', 'CO']
    cities = ['北京', '上海', '广州']
    
    # 重塑数据
    processed_data = []
    
    for city in cities:
        city_df = df[df['type'].isin(pollutants)].copy()
        city_pivot = city_df.pivot_table(
            index=['date', 'hour'],
            columns='type',
            values=city
        ).reset_index()
        
        # 添加城市标识
        city_pivot['city'] = city
        
        processed_data.append(city_pivot)
    
    # 合并所有城市数据
    final_df = pd.concat(processed_data)
    
    # 计算每日平均值
    daily_avg = final_df.groupby(['date', 'city']).mean().reset_index()
    
    # 将数据转换为宽格式，方便分析
    wide_df = daily_avg.pivot_table(
        index='date',
        columns='city',
        values=pollutants
    )
    
    # 扁平化多级列索引
    wide_df.columns = [f'{col[0]}_{col[1]}' for col in wide_df.columns]
    wide_df.reset_index(inplace=True)
    
    # 保存处理后的数据
    wide_df.to_csv(output_file, index=False)
    print(f"数据已处理并保存到 {output_file}")

# 使用示例
preprocess_data(r"D:\weather\weather\AQ_project\air.csv", r'D:\weather\weather\AQ_project\processed_air_quality.csv')